package com.it2297.model;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import com.it2297.main.App;

public class Needy extends User {

	private double HouseHoldIncome;
	private int FamilySize;
	private int numOfDependent; 

	public double getHouseHoldIncome() {
		return HouseHoldIncome;
	}

	public void setHouseHoldIncome(double houseHoldIncome) {
		HouseHoldIncome = houseHoldIncome;
	}

	public int getFamilySize() {
		return FamilySize;
	}

	public void setFamilySize(int familySize) {
		FamilySize = familySize;
	}

	public int getNumOfDependent() {
		return numOfDependent;
	}

	public void setNumOfDependent(int numOfDependent) {
		this.numOfDependent = numOfDependent;
	}

	
	public Needy getSingle(String nric) {

		try {
			App.mySqlConnection.open();

			ResultSet resultSet = App.mySqlConnection
					.executeQuery("SELECT * FROM americano.NEEDY_VIEW where nric = '" + nric + "' AND status ='ava'" );

			if (resultSet.next()) {

				setHouseHoldIncome(resultSet.getDouble("household_income"));
				setNumOfDependent(resultSet.getInt("num_of_dependant"));
				setFamilySize(resultSet.getInt("family_Size"));
				setNric(resultSet.getString("nric"));
				setAddress(resultSet.getString("address"));
				setName(resultSet.getString("name"));
				setPhone(resultSet.getString("phone"));
				setPassword(resultSet.getString("password"));
				setGender(resultSet.getString("gender"));
				setEmail(resultSet.getString("email"));
				setEthnic(resultSet.getString("ethnic"));
				setDob(resultSet.getDate("dob"));
				setStatus(resultSet.getString("status"));
				setCountryOfBirth(resultSet.getString("birth_country"));
				setDateRegister(resultSet.getTimestamp("date_register"));
				// needy.setFamilyDetails(familyDetails.get)

			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
		return this;
	}

	public static boolean createUser(User user) {

		boolean success = false;
		 
		//double HouseHoldIncome = needy.getHouseHoldIncome();
		//int NumOfDependent = needy.getNumOfDependent();
		//int FamilySize = needy.getFamilySize();

		String nric = user.getNric().toString();
		String name = user.getName().toString();
		String address = user.getAddress().toString();
		String Phone = user.getPhone().toString();
		String password = user.getPassword().toString();
	    String gender = user.getGender().toString();
		String email =user.getEmail().toString();
		String ethnic = user.getEthnic().toString();
		Date dob = user.getDob();
		String status = user.getStatus().toString();
		String countryOfBirth = user.getCountryOfBirth().toString();
		String inputrole = "NEEDY";
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		
		// Timestamp dateRegister = needy.getDateRegister();

		try {
			App.mySqlConnection.open();
			
			//String sql = "INSERT INTO americano.needy(household_income,num_of_dependant,family_Size) VALUES ( '"+ nric+ "','"+ name+ "','"+ address+ "','"+ Phone+ "','"+ email+ "','"+ status+ "','"+ countryOfBirth + "' )";
			String sql1 = "INSERT INTO americano.user(nric,name,address,phone,password,gender,email,ethnic,dob,status,birth_country, role) VALUES ( '"+ nric+ "','"+ name+ "','"+ address+ "','"+ Phone+ "','"+password+"','"+ gender+"','"+ email+ "','"+ ethnic+ "','"+ df.format(dob) + "','"+ status+ "','" + countryOfBirth + "', '" + inputrole +"' )";
			//sql += "VALUES('" + HouseHoldIncome + "','" + NumOfDependent
			//		+ "','" + FamilySize + "')";
			System.out.println(sql1);
			App.mySqlConnection.executeUpdate(sql1);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}

		return success;

	}
	
	
	public static boolean createNeedy(Needy needy) {

		boolean success = false;

		double HouseHoldIncome = needy.getHouseHoldIncome();
		int NumOfDependent = needy.getNumOfDependent();
		int FamilySize = needy.getFamilySize();

		String nric = needy.getNric().toString();
	   /*String name = needy.getName().toString();
		String address = needy.getAddress().toString();
		String Phone = needy.getPhone().toString();
		// char gender = needy.getGender();
		String email = needy.getEmail().toString();
		// String ethnic = needy.getEthnic().toString();
		// Date dob = needy.getDob();
		String status = needy.getStatus().toString();
		String countryOfBirth = needy.getCountryOfBirth().toString();
		// Timestamp dateRegister = needy.getDateRegister();
*/
		try {
			App.mySqlConnection.open();

			String sql = "INSERT INTO americano.needy(nric,household_income,num_of_dependant,family_Size)";
			sql += "VALUES('"+ nric+ "','" + HouseHoldIncome + "','" + NumOfDependent + "','" + FamilySize + "')";

			App.mySqlConnection.executeUpdate(sql);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}

		return success;

	}

	public static boolean updateUser(User user) {

		boolean success = false;

		//double HouseHoldIncome = needy.getHouseHoldIncome();
		//int NumOfDependent = needy.getNumOfDependent();
		//int FamilySize = needy.getFamilySize();

		String nric = user.getNric().toString();
		String name = user.getName().toString();
		String address = user.getAddress().toString();
		String Phone = user.getPhone().toString();
		String password = user.getPassword().toString();
		String gender = user.getGender().toString();
		String email = user.getEmail().toString();
		String ethnic = user.getEthnic().toString();
		//Date dob = user.getDob();
		String status = user.getStatus().toString();
		String countryOfBirth = user.getCountryOfBirth().toString();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		// Timestamp dateRegister = needy.getDateRegister();

		try {
			App.mySqlConnection.open();

			String sql = "UPDATE americano.user set nric= '" + nric + "', name = '" + name + "', address ='"+ address +"', phone ='"+ Phone +"',password ='"+ password +"',gender='" +gender+"',email='" +email+"',ethnic='"+ethnic+"',status='"+status+"',birth_country='"+countryOfBirth+"' WHERE nric = '" + nric + "'";
				
			App.mySqlConnection.executeUpdate(sql);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}

		return success;

	}
	
	
	public static boolean updateNeedy(Needy needy) {

		boolean success = false;

		double HouseHoldIncome = needy.getHouseHoldIncome();
		int NumOfDependent = needy.getNumOfDependent();
		int FamilySize = needy.getFamilySize();

		String nric = needy.getNric();
		//String name = needy.getName().toString();
		//String address = needy.getAddress().toString();
		//String Phone = needy.getPhone().toString();
		// char gender = needy.getGender();
		//String email = needy.getEmail().toString();
		// String ethnic = needy.getEthnic().toString();
		// Date dob = needy.getDob();
		//String status = needy.getStatus().toString();
		//String countryOfBirth = needy.getCountryOfBirth().toString();
		// Timestamp dateRegister = needy.getDateRegister();

		try {
			App.mySqlConnection.open();

			String sql = "UPDATE americano.needy set household_income= '" + HouseHoldIncome + "', num_of_dependant = '" + NumOfDependent + "',family_Size ='"+ FamilySize +"' WHERE nric = '" + nric + "'";
				
			App.mySqlConnection.executeUpdate(sql);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}

		return success;

	}
	
	public static boolean updateStatusToDel(User user) {
		boolean success = false;
		String nric1=user.getNric().toString();
		String status1="Inactive";
		
		try {
			App.mySqlConnection.open();
			String sql = "UPDATE americano.user SET status = 'ina' WHERE nric = '" +  user.getNric() +"'";
			
			App.mySqlConnection.executeUpdate(sql);
			success = true;
			
			String sql2="UPDATE americano.application SET status = '" + status1 + "'";
			sql2+= "WHERE fk_nric ='" +nric1+"'";
			App.mySqlConnection.executeUpdate(sql2);
			success = true;
			
			
		}
		catch(Exception ex){
			ex.printStackTrace();
		}
		finally{
			App.mySqlConnection.close();
		}
		return success;
	}
	
	public static ArrayList<Needy> retrieveSearch(Needy needy){
		ArrayList<Needy> arr_searchNeedy = new ArrayList<Needy>();
		ResultSet rs = null;
		
		String name = needy.getName().toString();
		
		try {
			App.mySqlConnection.open();
			String sql = "SELECT * FROM americano.NEEDY_VIEW WHERE name LIKE '"  + '%' + name + '%' + "' and status = 'ava'";
			rs=App.mySqlConnection.executeQuery(sql);
			
			
			if(!rs.next()){
				needy.setName("");
			}
			else{
				rs.beforeFirst();
				
					while(rs.next()){	
						Needy searchNeedy = new Needy();
						searchNeedy.setName(rs.getString("name"));	
						searchNeedy.setNric(rs.getString("nric"));
						arr_searchNeedy.add(searchNeedy);
					}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			App.mySqlConnection.close();
		}
		return arr_searchNeedy;
	}
	
	public static boolean IsNricExist(String nric){
		
		boolean result = false;
		ResultSet rs = null;
		String sql = "SELECT * FROM americano.user WHERE nric='" + nric + "' and status = 'ava'";
		
		try {
			App.mySqlConnection.open();
			rs = App.mySqlConnection.executeQuery(sql);
			if (rs.next()){
				result = true;
			System.out.println(result);
			}else{
				result = false;
				System.out.println(result);
			}
		} 
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			App.mySqlConnection.close();
		}
		System.out.println(result);
		return result;
	}
	
public static boolean IsNricExist2(String nric){
		
		boolean result = false;
		ResultSet rs = null;
		String sql = "SELECT * FROM americano.user WHERE nric='" + nric + "' and status = 'ava' and role='NEEDY'";
		
		try {
			App.mySqlConnection.open();
			rs = App.mySqlConnection.executeQuery(sql);
			if (rs.next()){
				result = true;
			System.out.println(result);
			}else{
				result = false;
				System.out.println(result);
			}
		} 
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			App.mySqlConnection.close();
		}
		System.out.println(result);
		return result;
	}


    public static boolean IsNameExist(String name){
		
		boolean result = false;
		ResultSet rs = null;
		String sql = "SELECT * FROM americano.NEEDY_VIEW WHERE name='" + name + "' and status = 'ava' and role = 'NEEDY'" ;
		
		try {
			App.mySqlConnection.open();
			rs = App.mySqlConnection.executeQuery(sql);
			if (rs.next()){
				result = true;
			System.out.println(result);
			}else{
				result = false;
				System.out.println(result);
			}
		} 
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			App.mySqlConnection.close();
		}
		System.out.println(result);
		return result;
	}
	

	

}
